MySQL 声明字段的时候，括号的数字到底是什么

发表于 | 分类于

整理了一下跟 MySQL 声明字段时，括号数字含义相关的资料。

之前碰到过一些关于 varchar(20) int(11) 这样的字段声明，对其中数组含义也未求甚解，每次都是现查现用。这次汇总了一下相关的含义，不同类型字段声明区别还是比较大的。记录如下：

Numeric Type

官方文档是：numeric-type-overview，摘录其中核心部分如下：

M indicates the maximum display width for integer types. The maximum display width is 255. Display width is unrelated to the range of values a type can contain, as described in Section 11.2, “Numeric Types”. For floating-point and fixed-point types, M is the total number of digits that can be stored.

  • 针对整数，是个 visit width
  • 针对浮点数，表示精度

更细节一些，针对 BIT

M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

针对 DECIMAL

A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the - sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.

针对 FLOAT

M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

针对 DOUBLE

M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

后面的区别主要在 M 的默认值上。

String Type

官方文档镇楼：String Type Overview

字符串的分为字符 and 字节两种，M 都表示了对当前数据类型 length 的限制。区别是：

  • 字符类型表示的是字符的个数；字节类型的表示的是字节的个数
  • M 的范围不一样，根据具体数据类型来
  • 如果是字符类型的话，还需要考虑字符集区别对 M 最大值的影响：比如 varchar，在 ASCII 下最大值是 65535；而 utf-8 之下就是 21844。更详细的可以参考：Limits on Table Column Count and Row Size

Date and Time Type

官方文档镇楼：Date and Time Type Overview

其中：

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

  • fsp 表示的是秒后面的精度是多少，值为0-6
  • YEAR 比较特殊，是个固定值 4